﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Text;
using com.magickms.util;

namespace com.magickms._db
{
    /// <summary>
    /// DBAccess 的摘要说明
    /// </summary>
    public class DBAccess
    {
        /// <summary>
        /// 当前要操作的字段名称（列表（以逗号“,”分隔符区分））
        /// </summary>
        private readonly StringBuilder _columList;

        /// <summary>
        /// 数据库查寻时的排序依据（列表（以逗号“,”分隔符区分））
        /// </summary>
        private readonly StringBuilder _orderList;

        /// <summary>
        /// 数据KEY-VALUE对持有变量
        /// </summary>
        private readonly List<string> _paramList;

        /// <summary>
        /// 符号持有变量
        /// </summary>
        private readonly List<string> _signList;

        /// <summary>
        /// 当前要操作的表格名称（列表（以逗号“,”分隔符区分））
        /// </summary>
        private readonly StringBuilder _tableList;

        /// <summary>
        /// 数据插入时数据列表（以逗号“,”分隔符区分）
        /// </summary>
        private readonly List<string> _valueList;

        /// <summary>
        /// 数据库操作时的关联条件列表（以逗号“,”分隔符区分）
        /// </summary>
        private readonly StringBuilder _whereList;
        /// <summary>
        /// Batch缓存
        /// </summary>
        private List<string> _batchList;

        private int _minLimit;
        private int _maxLimit;
        private readonly string _connStr;

        public DBAccess()
        {
            // 参数列表
            _paramList = new List<string>();
            // 关联符号
            _signList = new List<string>();
            // 数据列表
            _valueList = new List<string>();
            // 字段缓冲
            _columList = new StringBuilder();
            // 表格缓冲
            _tableList = new StringBuilder();
            // 关联每件缓冲
            _whereList = new StringBuilder();
            // 排序依据缓冲
            _orderList = new StringBuilder();
#if DEV
            connStr = @"Data Source=F:\app\MagicKms\dat\amon.db3";
#else
            _connStr = "Data Source=" + Environment.CurrentDirectory + "\\dat\\amon.db3";
#endif
        }

        private static string Text2DB(string text)
        {
            return text == null ? "" : text.Replace("'", "''");
        }

        /// <summary>
        /// 添加要使用的表格
        /// </summary>
        /// <param name="table"></param>
        public void AddTable(string table)
        {
            if (Util.IsValidate(table))
            {
                _tableList.Append(", ").Append(table);
            }
        }

        /// <summary>
        /// 添加要使用的表格
        /// </summary>
        /// <param name="table"></param>
        /// <param name="alies"></param>
        public void AddTable(string table, string alies)
        {
            if (Util.IsValidate(table))
            {
                _tableList.Append(", ").Append(table).Append(" AS ").Append(alies);
            }
        }

        /// <summary>
        /// 添加带连接的表格
        /// </summary>
        /// <param name="tableLeft"></param>
        /// <param name="tableRight"></param>
        /// <param name="joinStyle"></param>
        public void AddTable(string tableLeft, string tableRight, string joinStyle)
        {
            _tableList.Append(", ").Append(tableLeft).Append(' ').Append(joinStyle).Append(' ').Append(tableRight);
        }

        /// <summary>
        /// 添加带连接的表格
        /// </summary>
        /// <param name="tableLeft"></param>
        /// <param name="tableRight"></param>
        /// <param name="joinStyle"></param>
        /// <param name="columnLeft"></param>
        /// <param name="columnRight"></param>
        public void AddTable(string tableLeft,
                             string tableRight,
                             string joinStyle,
                             string columnLeft,
                             string columnRight)
        {
            _tableList.Append(", ").Append(tableLeft).Append(' ').Append(joinStyle).Append(' ').Append(tableRight);
            _tableList.Append(" ON ").Append(columnLeft).Append(" = ").Append(columnRight);
        }

        /// <summary>
        /// 添加查寻字段<br></br>
        /// SELECT colname1, colname2, ... FROM tablename
        /// </summary>
        /// <param name="colname">对于于要查寻的表格的相关字段</param>
        public void AddColumn(string colname)
        {
            if (Util.IsValidate(colname))
            {
                _columList.Append(", ").Append(colname);
            }
        }

        /// <summary>
        /// 添加查寻字段<br></br>
        /// SELECT colname1 AS col1, colname2 AS col2, ... FROM tablename
        /// </summary>
        /// <param name="colname">对于于要查寻的表格的相关字段</param>
        /// <param name="alies">字段别名</param>
        public void AddColumn(string colname, string alies)
        {
            if (Util.IsValidate(colname))
            {
                _columList.Append(", ").Append(colname).Append(" AS ").Append(alies);
            }
        }

        /// <summary>
        /// 数据更新：添加数据库操作的KEY-VALUE对。<br/>
        /// UPDATE SET key1=value1, key2=value2 ... 
        /// </summary>
        /// <param name="key">数据库字段名</param>
        /// <param name="value">对应字段的值</param>
        public void AddParam(string key, string value)
        {
            AddParam(key, value, true);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="key"></param>
        /// <param name="value"></param>
        public void AddParam(string key, long value)
        {
            AddParam(key, "=", value);
        }

        /// <summary>
        /// 数据更新：添加数据库操作的KEY-VALUE对。
        /// UPDATE SET key1=value1, key2=value2 ... 
        /// </summary>
        /// <param name="key">数据库字段名</param>
        /// <param name="value">对应字段的值</param>
        /// <param name="isLiteral">当前字段是否为纯文本，true为纯文本，false为其它格式。</param>
        public void AddParam(string key, string value, bool isLiteral)
        {
            AddParam(key, "=", value, isLiteral);
        }

        /// <summary>
        /// 数据更新：添加数据库操作的KEY-VALUE对。
        /// </summary>
        /// <param name="param">数据库字段名</param>
        /// <param name="sign">运算操作符</param>
        /// <param name="value">对应字段的值</param>
        /// <param name="isLiteral">当前字段是否为纯文本，true为纯文本，false为其它格式。</param>
        public void AddParam(string param, string sign, string value, bool isLiteral)
        {
            if (Util.IsValidate(param))
            {
                _paramList.Add(param);
                _signList.Add(sign);

                // 值信息处理
                if (value == null)
                {
                    _valueList.Add("NULL");
                }
                else if (isLiteral)
                {
                    _valueList.Add(" '" + Text2DB(value) + "'");
                }
                else
                {
                    _valueList.Add(value);
                }
            }
        }

        /// <summary>
        /// 数据更新：添加数据库操作的KEY-VALUE对。
        /// </summary>
        /// <param name="param">数据库字段名</param>
        /// <param name="sign">运算操作符</param>
        /// <param name="value">对应字段的值</param>
        public void AddParam(string param, string sign, long value)
        {
            _paramList.Add(param);
            _signList.Add(sign);
            _valueList.Add(value.ToString());
        }

        /// <summary>
        /// 用户自定义关联条件
        /// </summary>
        /// <param name="where"></param>
        public void AddWhere(string where)
        {
            _whereList.Append(" AND (").Append(where).Append(") ");
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="key"></param>
        /// <param name="value"></param>
        public void AddWhere(string key, long value)
        {
            _whereList.Append(" AND ").Append(key).Append('=').Append(value);
        }

        /// <summary>
        /// 添加WHERE查寻条件，默认运算符为等号“=”，默认值为纯文本<br/>
        /// UPDATE tablename SET ... WHERE key1=value1 AND key2=value2 ...
        /// </summary>
        /// <param name="key">参照数据库表格字段名</param>
        /// <param name="value">参照值</param>
        public void AddWhere(string key, string value)
        {
            AddWhere(key, value, true);
        }

        /// <summary>
        /// 添加WHERE查寻条件，默认运算符为等号“=”<br/>
        /// UPDATE tablename SET ... WHERE key1=value1 AND key2=value2 ...
        /// </summary>
        /// <param name="key">参照数据库表格字段名</param>
        /// <param name="value">参照值</param>
        /// <param name="isLiteral">是否为纯文本字符串，true为纯文本，false为非文本</param>
        public void AddWhere(string key, string value, bool isLiteral)
        {
            AddWhere(key, "=", value, isLiteral);
        }

        /// <summary>
        /// 添加WHERE查寻条件<br/>
        /// UPDATE tablename SET ... WHERE key1=value1 AND key2=value2 ...
        /// </summary>
        /// <param name="key">参照数据库表格字段名</param>
        /// <param name="sign">参照运算符，如+、-、=、!=等</param>
        /// <param name="value">参照值</param>
        /// <param name="isLiteral">是否为纯文本字符串，true为纯文本，false为非文本</param>
        public void AddWhere(string key, string sign, string value, bool isLiteral)
        {
            if (Util.IsValidate(key) && Util.IsValidate(sign))
            {
                _whereList.Append(" AND ").Append(key);
                _whereList.Append(" ").Append(sign);

                // 值信息处理
                if (value == null)
                {
                    _whereList.Append("NULL");
                }
                else if (isLiteral)
                {
                    _whereList.Append(" '").Append(Text2DB(value)).Append("'");
                }
                else
                {
                    _whereList.Append(" ").Append(value);
                }
            }
        }

        /// <summary>
        /// 默认升序排序
        /// </summary>
        /// <param name="key"></param>
        public void AddSort(string key)
        {
            AddSort(key, true);
        }

        /// <summary>
        /// 添加排序依据<br/>
        /// SELECT * FROM tablename WHERE ... ORDER BY key1 value1, key2 value2, ...
        /// </summary>
        /// <param name="key">参照数据库表格字段</param>
        /// <param name="asc">排序方法:true:ASC表示升序;false:DESC表示降序</param>
        public void AddSort(string key, bool asc)
        {
            if (Util.IsValidate(key))
            {
                _orderList.Append(", ").Append(key);
                _orderList.Append(" ").Append(asc ? "ASC" : "DESC");
            }
        }

        public void AddLimit(int limit)
        {
            AddLimit(0, limit);
        }

        public void AddLimit(int min, int max)
        {
            if (min < 0 || max < min)
            {
                return;
            }

            _minLimit = min;
            _maxLimit = max;
        }

        /// <summary>
        /// 其它相关的数据库操作，如COMMIT、HSQL专有的SHUTDOWN等。
        /// </summary>
        /// <param name="sql">要执行的SQL语句</param>
        /// <returns>当前操作是否成功</returns>
        public bool Execute(string sql)
        {
            using (var conn = new SQLiteConnection(_connStr))
            {
                conn.Open();
                var comm = conn.CreateCommand();
                comm.CommandText = sql;
                comm.CommandType = CommandType.Text;

                comm.ExecuteNonQuery();
            }
            return true;
        }

        /// <summary>
        /// 数据库查寻
        /// </summary>
        /// <returns>查寻结果集</returns>
        public DataTable ExecuteSelect()
        {
            return ExecuteSelect(GetSelectSQL());
        }

        /// <summary>
        /// 数据库查寻
        /// </summary>
        /// <param name="sqlSelect">查寻语句</param>
        /// <returns>查寻结果集</returns>
        public DataTable ExecuteSelect(string sqlSelect)
        {
            var dataList = new DataTable();

            using (var conn = new SQLiteConnection(_connStr))
            {
                conn.Open();

                var comm = conn.CreateCommand();
                comm.CommandText = sqlSelect;
                comm.CommandType = CommandType.Text;

                var adapter = new SQLiteDataAdapter(comm);
                adapter.Fill(dataList);
            }

            return dataList;
        }

        /// <summary>
        /// 数据库更新
        /// </summary>
        /// <returns>当前操作所影响的行数</returns>
        public int ExecuteUpdate()
        {
            return ExecuteUpdate(GetUpdateSQL());
        }

        /// <summary>
        /// 数据库更新
        /// </summary>
        /// <param name="sqlUpdate">更新语句</param>
        /// <returns>当前操作所影响的行数</returns>
        public int ExecuteUpdate(string sqlUpdate)
        {
            int cnt;
            using (var conn = new SQLiteConnection(_connStr))
            {
                conn.Open();
                using (var tran = conn.BeginTransaction())
                {
                    using (var comm = new SQLiteCommand(conn))
                    {
                        comm.CommandText = sqlUpdate;
                        comm.CommandType = CommandType.Text;

                        cnt = comm.ExecuteNonQuery();
                    }
                    tran.Commit();
                }
            }
            return cnt;
        }

        /// <summary>
        /// 数据库插入
        /// </summary>
        /// <returns>当前操作所影响的行数</returns>
        public int ExecuteInsert()
        {
            return ExecuteInsert(GetInsertSQL());
        }

        /// <summary>
        /// 数据库插入
        /// </summary>
        /// <param name="sqlInsert">插入语句</param>
        /// <returns>当前操作所影响的行数</returns>
        public int ExecuteInsert(string sqlInsert)
        {
            int cnt;
            using (var conn = new SQLiteConnection(_connStr))
            {
                conn.Open();
                using (var tran = conn.BeginTransaction())
                {
                    using (var comm = new SQLiteCommand(conn))
                    {
                        comm.CommandText = sqlInsert;
                        comm.CommandType = CommandType.Text;

                        cnt = comm.ExecuteNonQuery();
                    }
                    tran.Commit();
                }
            }
            return cnt;
        }

        /// <summary>
        /// 数据库删除
        /// </summary>
        /// <returns>当前操作所影响的行数</returns>
        public int ExecuteDelete()
        {
            return ExecuteDelete(GetDeleteSQL());
        }

        /// <summary>
        /// 数据库删除
        /// </summary>
        /// <param name="sqlDelete">删除语句</param>
        /// <returns>当前操作所影响的行数</returns>
        public int ExecuteDelete(string sqlDelete)
        {
            int cnt;
            using (var conn = new SQLiteConnection(_connStr))
            {
                conn.Open();
                using (var tran = conn.BeginTransaction())
                {
                    using (var comm = new SQLiteCommand(conn))
                    {
                        comm.CommandText = sqlDelete;
                        comm.CommandType = CommandType.Text;

                        cnt = comm.ExecuteNonQuery();
                    }
                    tran.Commit();
                }
            }
            return cnt;
        }

        /**
         * 
         * @param sql
         * @throws SQLException
         */
        public void AddBatch(string sql)
        {
            if (_batchList == null)
            {
                _batchList = new List<string>();
            }
            _batchList.Add(sql);
        }
        /**
         * 
         * @throws SQLException
         */
        public void AddDeleteBatch()
        {
            AddBatch(GetDeleteSQL());
        }

        /**
         * 
         * @throws SQLException
         */
        public void AddInsertBatch()
        {
            AddBatch(GetInsertSQL());
        }

        public void AddUpdateBatch()
        {
            AddBatch(GetUpdateSQL());
        }

        public void ExecuteBatch()
        {
            ExecuteBatch(_batchList);
        }

        public void ExecuteBatch(List<string> sqlList)
        {
            using (var conn = new SQLiteConnection(_connStr))
            {
                conn.Open();
                using (var tran = conn.BeginTransaction())
                {
                    using (var comm = new SQLiteCommand(conn))
                    {
                        comm.CommandType = CommandType.Text;
                        foreach (var sql in sqlList)
                        {
                            comm.CommandText = sql;
                            comm.ExecuteNonQuery();
                        }
                    }
                    tran.Commit();
                }
            }
        }

        /// <summary>
        /// 在保持同一个连接的基础上，重新设置查寻SQL语句，以进行新的数据库操作
        /// </summary>
        public void Reset()
        {
            _paramList.Clear();
            _signList.Clear();
            _valueList.Clear();
            _minLimit = 0;
            _maxLimit = 0;

            _columList.Remove(0, _columList.Length);
            _tableList.Remove(0, _tableList.Length);
            _whereList.Remove(0, _whereList.Length);
            _orderList.Remove(0, _orderList.Length);
        }

        /// <summary>
        /// 获取数据库查寻SQL语句
        /// </summary>
        /// <returns>SQL语句，若操作表格为空，则返回空语句</returns>
        private string GetSelectSQL()
        {
            // 数据合法性判断
            if (_tableList.Length < 1)
            {
                return "";
            }

            var sqlBuf = new StringBuilder();

            // 查寻字段拼接
            sqlBuf.Append("SELECT ");
            sqlBuf.Append(_columList.Length > 0 ? _columList.ToString().Substring(2) : "*");

            // 查寻表格拼接
            sqlBuf.Append(" FROM ").Append(_tableList.ToString().Substring(2));

            // 查寻关联条件拼接
            if (_whereList.Length > 0)
            {
                sqlBuf.Append(" WHERE ").Append(_whereList.ToString().Substring(5));
            }

            // 排序依据拼接
            if (_orderList.Length > 0)
            {
                sqlBuf.Append(" ORDER BY ").Append(_orderList.ToString().Substring(2));
            }

            if (_maxLimit > 0)
            {
                sqlBuf.Append(" LIMIT ").Append(_minLimit).Append(',').Append(_maxLimit);
            }

            return sqlBuf.ToString();
        }

        /// <summary>
        /// 获取数据库更新SQL语句
        /// </summary>
        /// <returns>SQL语句，若操作表格为空，则返回空语句</returns>
        private string GetUpdateSQL()
        {
            // 数据合法性判断
            if (_tableList.Length < 1 || _paramList.Count < 1)
            {
                return "";
            }

            var sqlBuf = new StringBuilder();

            // 更新表格拼接
            sqlBuf.Append("UPDATE ").Append(_tableList.ToString().Substring(2)).Append(" SET ");

            // 更新字段及值拼接
            var cnt = _paramList.Count - 1;
            for (var idx = 0; idx < cnt; idx += 1)
            {
                sqlBuf.Append(_paramList[idx]);
                sqlBuf.Append(_signList[idx]);
                sqlBuf.Append(_valueList[idx]);
                sqlBuf.Append(", ");
            }
            sqlBuf.Append(_paramList[cnt]);
            sqlBuf.Append(_signList[cnt]);
            sqlBuf.Append(_valueList[cnt]);

            // 参照条件拼接
            if (_whereList.Length > 0)
            {
                sqlBuf.Append(" WHERE ").Append(_whereList.ToString().Substring(5));
            }
            return sqlBuf.ToString();
        }

        /// <summary>
        /// 获取数据库插入SQL语句
        /// </summary>
        /// <returns>SQL语句，若操作表格为空，则返回空语句</returns>
        private string GetInsertSQL()
        {
            // 数据合法性判断
            if (_tableList.Length < 1)
            {
                return "";
            }

            var sqlBuf = new StringBuilder();

            // 数据库表格拼接
            sqlBuf.Append(" INSERT INTO ").Append(_tableList.ToString().Substring(2));

            // 要插入字段拼接
            sqlBuf.Append(" (");
            var cnt = _paramList.Count - 1;
            for (var idx = 0; idx < cnt; idx += 1)
            {
                sqlBuf.Append(_paramList[idx]);
                sqlBuf.Append(", ");
            }
            sqlBuf.Append(_paramList[cnt]);
            sqlBuf.Append(")");

            // 插入数据拼接
            sqlBuf.Append(" VALUES (");
            cnt = _valueList.Count - 1;
            for (var idx = 0; idx < cnt; idx += 1)
            {
                sqlBuf.Append(_valueList[idx]);
                sqlBuf.Append(", ");
            }
            sqlBuf.Append(_valueList[cnt]);
            sqlBuf.Append(")");

            return sqlBuf.ToString();
        }

        /// <summary>
        /// 获取数据库删除SQL语句
        /// </summary>
        /// <returns>SQL语句，若操作表格为空，则返回空语句</returns>
        private string GetDeleteSQL()
        {
            // 数据合法性判断
            if (_tableList.Length < 1)
            {
                return "";
            }

            var sqlBuf = new StringBuilder();

            // 数据库表格拼接
            sqlBuf.Append("DELETE FROM ").Append(_tableList.ToString().Substring(2));

            // 关联条件拼接
            sqlBuf.Append(" WHERE ").Append(_whereList.ToString().Substring(5));

            return sqlBuf.ToString();
        }
    }
}
